﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DONGACS.Common;
using DONGACS.Domain;

namespace DONGACS.DAO
{
    public class UsersDao: AccessData
    {
        public IList<Users> GetAllUsersActive()
        {
            CommandText = "Select * from TB_Users U Where U.IsDelete = 0 and U.IsActive = 1";
            Commandtype = CommandType.Text;

            DataTable table = GetDataTable();

            IList<Users> list = CollectionHelper.ConvertTo<Users>(table);

            return list;
        }

        /// <summary>
        /// Lấy tất cả user trong một phòng ban xác định
        /// </summary>
        /// <param name="departmentId"></param>
        /// <returns></returns>
        public IList<Users> GetAllUsersActiveInDepartment(string departmentId)
        {
            CommandText = "Select * from TB_Users U Where U.IsDelete = 0 and U.IsActive = 1 and U.Department  = " + Convert.ToInt32(departmentId) ;
            Commandtype = CommandType.Text;

            DataTable table = GetDataTable();

            IList<Users> list = CollectionHelper.ConvertTo<Users>(table);

            return list;
        }

        public IList<Users> GetAllUsers()
        {
            CommandText = "Select * from TB_Users U Where U.IsDelete = 0 Order By U.ID DESC";
            Commandtype = CommandType.Text;

            DataTable table = GetDataTable();

            IList<Users> list = CollectionHelper.ConvertTo<Users>(table);

            return list;
        }

        public Users GetUsersByUserName(string userName)
        {
            CommandText = "Select * from TB_Users U Where U.IsDelete = 0 and U.UserName = '" + userName + "'";
            Commandtype = CommandType.Text;

            DataTable table = GetDataTable();

            //Users user = CollectionHelper.CreateItem<Users>(table.Rows[0]);

            return table.Rows.Count != 0 ? CollectionHelper.CreateItem<Users>(table.Rows[0]) : null;
        }

        public Users GetIsActiveUsersById(string id)
        {
            CommandText = "Select * from TB_Users U Where U.IsDelete = 0 and U.IsActive = 1 and U.ID = " + id;
            Commandtype = CommandType.Text;

            DataTable table = GetDataTable();

            Users user = CollectionHelper.CreateItem<Users>(table.Rows[0]);

            return user;
        }

        public Users GetUsersById(string Id)
        {
            CommandText = "Select * from TB_Users U Where U.IsDelete = 0 and U.ID = " + Id;
            Commandtype = CommandType.Text;

            DataTable table = GetDataTable();

            return table.Rows.Count != 0 ? CollectionHelper.CreateItem<Users>(table.Rows[0]) : null;

        }

        public bool AddNewUsers(Users users)
        {
            return Insert<Users>("TB_Users", users);
        }

        public bool AddNewUsers(Users users, ref int idReturn)
        {
            return Insert<Users>("TB_Users", users, ref idReturn);
        }

        public bool Update(Users users)
        {
            return Update("TB_Users", users);
        }

        public bool Delete(string userID)
        {
            return Delete<Users>("TB_Users", userID);
        }

        //Truy vấn dùng Parameter

        public Users GetUsersByUserNamePassword(string usersname, string password)
        {
            CommandText = "Select * From TB_Users U Where U.UserName = '" + @usersname + "' and U.Password = '" + @password +
                                  "' and U.IsDelete = 0";

            //truyền tham số và giá trị tham số cho truy vấn
            ParameterCollection = new string[] { "@usersname", "@password" };
            ValuesCollection = new object[] { usersname, password };
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table.Rows.Count != 0 ? CollectionHelper.CreateItem<Users>(table.Rows[0]) : null;
        }

        //public Users GetUsersByUserNamePassword(string usersname, string password)
        //{
        //    CommandText = "Select * From TB_Users U Where U.UserName = '" + @usersname + "' and U.Password = '" + @password +
        //                          "' and U.IsActive = 1 and U.IsDelete = 0";

        //    //truyền tham số và giá trị tham số cho truy vấn
        //    ParameterCollection = new string[] { "@usersname", "@password" };
        //    ValuesCollection = new object[] { usersname, password };

        //    Commandtype = CommandType.Text;

        //    DataTable table = GetDataTable();

        //    return table.Rows.Count != 0 ? CollectionHelper.CreateItem<Users>(table.Rows[0]) : null;
        //}

        //Sử dụng store
        //public Users GetUsersByUserNamePassword(string usersname, string password)
        //{
        //    //khai báo store sử dụng
        //    CommandText = "sp_GetUsersByUserNamePassword";

        //    //truyền tham số và giá trị tham số cho store
        //    ParameterCollection = new string[] { "@usersname", "@password" };
        //    ValuesCollection = new object[] { usersname, password };

        //    Commandtype = CommandType.StoredProcedure;

        //    DataTable table = GetDataTable();

        //    return table.Rows.Count != 0 ? CollectionHelper.CreateItem<Users>(table.Rows[0]) : null;
        //}


        //Truyền đối số
        //public Users GetUsersByUserNamePassword(string usersname, string password)
        //{
        //    CommandText = "Select * From TB_Users U Where U.UserName = '" + usersname + "' and U.Password = '" + password +
        //                  "' and U.IsActive = 1 and U.IsDelete = 0";

        //    Commandtype = CommandType.Text;
        //    DataTable table = GetDataTable();

        //    return table.Rows.Count != 0 ? CollectionHelper.CreateItem<Users>(table.Rows[0]) : null;
        //}

      

    }
}
